package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import pojo.Food;
import pojo.Merchant;
import pojo.MerchantClient;
import pojo.MerchantDto;
import util.DBUtils;
import dao.MerchantDao;

public class MerchantDaoImpl extends BaseDaoImpl<Merchant> implements MerchantDao {

	@Override
	public int add(Merchant merchant) {
		String sql = "insert into merchants(id,name,address,id_card,avatar,password) values(?,?,?,?,?,?)";
		Connection con=null;
		PreparedStatement pst = null;
		con = DBUtils.createConnection();
		int m = 0;
		try {
			con=DBUtils.createConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, merchant.getId());
			pst.setString(2, merchant.getName());
			pst.setString(3, merchant.getAddress());
			pst.setString(4, merchant.getIdCard());
			pst.setString(5, merchant.getAvatar());
			pst.setString(6, merchant.getPassword());
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		finally {
			DBUtils.close(con, pst, null);
		}
		return m;
	}

	@Override
	public int delete(String id) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int update(Merchant t) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Merchant load(String id) {
		
		return null;
	}

	@Override
	public List<Merchant> findAll() {
		List<Merchant> list = null;
		String sql = "select * from MERCHANTS";
		System.out.println("in all findall....");
		PreparedStatement pst = null;
		ResultSet rs = null;
		Connection con = DBUtils.createConnection();
		
		try {
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			list = new ArrayList<Merchant>();
			while(rs.next()){
				System.out.println("test....");
				Merchant b = new Merchant();
				b.setId(rs.getString("id"));
				b.setName(rs.getString("name"));
				b.setAddress(rs.getString("address"));
				b.setAvatar(rs.getString("avatar"));
				list.add(b);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtils.close(con, pst, rs);
		}
		return list;
	}
	
	public List<MerchantClient> findByType(String type, List<MerchantDto> correctMerchants) {
		System.out.println(type);
		List<MerchantClient> list = new ArrayList<MerchantClient>();
		PreparedStatement pst = null;
		ResultSet rs = null;
		String sql = null;
		Connection con = DBUtils.createConnection();
		
		StringBuilder merchantLiString = new StringBuilder();
		if (correctMerchants == null) {
			return null;
		}
		for(MerchantDto  m:correctMerchants){
			merchantLiString.append("'"+ m.getName() +"'"+",");
		}
		if("all".equals(type))
			sql = "select id, name from (select id, name from merchants where id in (select m_id from foods)) where name in ("+merchantLiString.substring(0, merchantLiString.length()-1) +")";
		else
			sql = "select id, name from (select id, name from merchants where id in (select m_id from foods where c_id=?)) "
					+ "where name in ("+merchantLiString.substring(0, merchantLiString.length()-1) +")";
		
		try {
			pst = con.prepareStatement(sql);
			if(!"all".equals(type)){
				pst.setString(1, type);
			}
			rs = pst.executeQuery();
			while(rs.next()){
				MerchantClient b = new MerchantClient();
				b.setId(rs.getString("id"));
				b.setName(rs.getString("name"));
				list.add(b);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtils.close(con, pst, rs);
		}
		
		Map<String, MerchantDto> map = new HashMap<String,MerchantDto>();
		
		for(int i=0;i<correctMerchants.size();i++){
			map.put(correctMerchants.get(i).getName(),correctMerchants.get(i));
		}
		
		for(MerchantDto merchantDto:correctMerchants){
			String name = merchantDto.getName();
			for(MerchantClient m:list){
				if(name.equals(m.getName())){
					m.setAddress(merchantDto.getAddress());
					m.setAvatar(merchantDto.getAvatar());
				}
			}
		}
		return list;
	}
	
	public List<MerchantClient> findByTypePage(int current, int pageSize, String type, List<MerchantDto> correctMerchants) {
		int begin = (current - 1) * pageSize + 1;
	    int end = begin + pageSize - 1;
		List<MerchantClient> list = new ArrayList<MerchantClient>();
		String sql = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		Connection con = DBUtils.createConnection();
		StringBuilder merchantLiString = new StringBuilder();
		if (correctMerchants == null) {
			return null;
		}
		for(MerchantDto  m:correctMerchants){
			merchantLiString.append("'"+m.getName()+"'"+",");
		}
		
		try {
			if("all".equals(type))
				sql = "select id, name from (select id, name from merchants where id in (select m_id from foods)) where name in ("+merchantLiString.substring(0, merchantLiString.length()-1) +")";
			else
				sql = "select id, name from (select id, name from merchants where id in (select m_id from foods where c_id=?)) "
						+ "where name in ("+merchantLiString.substring(0, merchantLiString.length()-1) +")";
			String sql1 = "select * from(select rownum as rn, f.* from ("+ sql +") f) temp where temp.rn between ? and ?";
			pst = con.prepareStatement(sql1);
			
			if(!"all".equals(type)){
				pst.setString(1, type);
				pst.setInt(2, begin);
				pst.setInt(3, end);
			}
			else{
				pst.setInt(1, begin);
				pst.setInt(2, end);
			}
			rs = pst.executeQuery();
			while(rs.next()){
				MerchantClient b = new MerchantClient();
				b.setId(rs.getString("id"));
				b.setName(rs.getString("name"));
				list.add(b);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtils.close(con, pst, rs);
		}
		
		Map<String, MerchantDto> map = new HashMap<String,MerchantDto>();
		
		for(int i=0;i<correctMerchants.size();i++){
			map.put(correctMerchants.get(i).getName(),correctMerchants.get(i));
		}
		
		for(MerchantDto merchantDto:correctMerchants){
			String name = merchantDto.getName();
			for(MerchantClient m:list){
				if(name.equals(m.getName())){
					m.setAddress(merchantDto.getAddress());
					m.setAvatar(merchantDto.getAvatar());
				}
			}
		}
		return list;
	}
	
	public List<Merchant> selectPage(int current, int pageSize) {
        
		String sql = "select * from(select rownum as rn, f.* from merchants f) temp where temp.rn between ? and ?";
        int begin = (current - 1) * pageSize + 1;
        int end = begin + pageSize - 1;
        List<Merchant> list = new ArrayList<Merchant>();
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            con = DBUtils.createConnection();
            pst = con.prepareStatement(sql);
            pst.setInt(1, begin);
            pst.setInt(2, end);
            rs = pst.executeQuery();
            Merchant merchant = null;
            while (rs.next()) {
                merchant = new Merchant();
                merchant.setId(rs.getString("id"));
				merchant.setName(rs.getString("name"));
				merchant.setAddress(rs.getString("address"));
				merchant.setAvatar(rs.getString("avatar"));
				list.add(merchant);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(con, pst, rs);
        }
        return list;
    }

	
	public int getCount(String type, List<MerchantDto> correctMerchants) {
		String sql1=null;
		StringBuilder merchantLiString = new StringBuilder();
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		if (correctMerchants == null) {
			return 0;
		}
		for(MerchantDto  m:correctMerchants){
			merchantLiString.append("'"+m.getName()+"'"+",");
		}
		if("all".equals(type))
			sql1 = "select id, name from (select id, name from merchants where id in (select m_id from foods)) where name in ("+merchantLiString.substring(0, merchantLiString.length()-1) +")";
		else
			sql1 = "select id, name from (select id, name from merchants where id in (select m_id from foods where c_id=?)) "
					+ "where name in ("+merchantLiString.substring(0, merchantLiString.length()-1) +")";
       
		String sql = "select count(0) as c from ("+ sql1 +")";
        int count = 0;
        try {
            con = DBUtils.createConnection();
            pst = con.prepareStatement(sql);
    		if(!"all".equals(type)){
    			try {
    				pst.setString(1, type);
    			} catch (SQLException e) {
    				e.printStackTrace();
    			}
    		}
            rs = pst.executeQuery();
            while (rs.next()) {
                count = rs.getInt("c");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(con, pst, rs);
        }
        return count;
	}
	
	public static void main(String[] args) {
		FoodsDaoImpl impl = new FoodsDaoImpl();
		MerchantDaoImpl dao = new MerchantDaoImpl();
		for(int i = 15; i < 20; i++){
			String id = UUID.randomUUID().toString();

			Food food = new Food("foodid_" +i, "food_" + i, 15, "test12",
					"d8d12466-691c-4632-915c-78da871b9cb7", "85d71308-b5d7-40f3-959d-7994be5402c6");
//			Merchant merchant = new Merchant(id, "hello"+i, "addr",
//					"id_card", "avatar", "pp");

			impl.add(food);
//			dao.add(merchant);
			// 93ae7d1e-ffcc-43af-b8b4-df1015a44231 hello0 id_card

		}
		//-- 85d71308-b5d7-40f3-959d-7994be5402c6 hello7  id_card avatar  pp     addr
	}
}
